﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using cwtrock.util;
using cwtrock.model;
using System.Data.SqlClient;

namespace cwtrock.dao.impl
{
    class AccountsDaoImpl : BasicDaoImpl
    {
        private SqlConnection conn;

        public int save(Accounts acct)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("INSERT INTO [ACCOUNTS]");
                strSql.Append(" ( [ABID] ,[ACCT_CODE] ,[ACCT_NAME] ");
                strSql.Append("  ,[PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE] ,[ACCT_CATALOG])");
                strSql.Append("   VALUES ( @abid, @acctcode, @acctname, @parentid, @acctbalancetype ,@accttype,@acctcatalog) ");

                SqlParameter[] parameters = { 
                new SqlParameter("@abid", SqlDbType.BigInt,50),
                new SqlParameter("@acctcode", SqlDbType.VarChar,50),
                new SqlParameter("@acctname", SqlDbType.VarChar,50),
                new SqlParameter("@parentid", SqlDbType.BigInt,50),
                new SqlParameter("@acctbalancetype", SqlDbType.VarChar,50),
                new SqlParameter("@accttype", SqlDbType.VarChar,10),
                new SqlParameter("@acctcatalog", SqlDbType.VarChar,50)
                 };

                parameters[0].Value = acct.Abid;
                parameters[1].Value = acct.Acctcode;
                parameters[2].Value = acct.Acctname;
                parameters[3].Value = acct.Parentid;
                parameters[4].Value = acct.Acctbalancetype;
                parameters[5].Value = acct.Accttype;
                parameters[6].Value = acct.Acctcatalog;


                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
                cmd.Parameters.Add(parameters[0]);
                cmd.Parameters.Add(parameters[1]);
                cmd.Parameters.Add(parameters[2]);
                cmd.Parameters.Add(parameters[3]);
                cmd.Parameters.Add(parameters[4]);
                cmd.Parameters.Add(parameters[5]);
                cmd.Parameters.Add(parameters[6]);

                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
            catch (Exception e)
            {
                throw e;
                return 0;
            }
            finally
            {
                ConnectionPool.getPool().closeConnection(conn);
            }
        }

        public int updateAccounts(Accounts acct)
        {
            try
            {
                String sql = "UPDATE [ACCOUNTS] SET [ABID] = @abid ,[ACCT_CODE] = @acctCode ,[ACCT_NAME] = @acctName ,[PARENTID] = @parentid ,[ACCT_BALANCE_TYPE] = @acctBalanceType ,[ACCT_TYPE] = @acctType ,[ACCT_CATALOG] = @acctCatalog WHERE [ID]=@id";
                SqlParameter[] parameters ={
                    new SqlParameter("@id",SqlDbType.Int),
                    new SqlParameter("@abid",SqlDbType.Int) ,
                    new SqlParameter("@acctCode",SqlDbType.VarChar,50),
                    new SqlParameter("@acctName",SqlDbType.VarChar,50),
                    new SqlParameter("@parentid",SqlDbType.VarChar,50),
                    new SqlParameter("@acctBalanceType",SqlDbType.VarChar,50),
                    new SqlParameter("@acctType",SqlDbType.VarChar,50),
                    new SqlParameter("@acctCatalog",SqlDbType.VarChar,50)
                    
                };
                parameters[0].Value = acct.Id;
                parameters[1].Value = acct.Abid;
                parameters[2].Value = acct.Acctcode;
                parameters[3].Value = acct.Acctname;
                parameters[4].Value = acct.Parentid;
                parameters[5].Value = acct.Acctbalancetype;
                parameters[6].Value = acct.Accttype;
                parameters[7].Value = acct.Acctcatalog;

                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(parameters[0]);
                cmd.Parameters.Add(parameters[1]);
                cmd.Parameters.Add(parameters[2]);
                cmd.Parameters.Add(parameters[3]);
                cmd.Parameters.Add(parameters[4]);
                cmd.Parameters.Add(parameters[5]);
                cmd.Parameters.Add(parameters[6]);
                cmd.Parameters.Add(parameters[7]);

                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;

            }
            catch (Exception e)
            {
                throw e;
                return 0;
            }
            finally
            {
                ConnectionPool.getPool().closeConnection(conn);
            }
        }

        public List<Accounts> getAcctsByCatalog(string catalog)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            strSql.Append(" [ACCT_CODE] ,[ACCT_NAME], ");
            strSql.Append(" [PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE] FROM [ACCOUNTS]");
            strSql.Append("  where [ACCT_CATALOG] = @catalog  order by [ACCT_CODE] ");

            SqlParameter[] parameters = { 
                new SqlParameter("@catalog", SqlDbType.VarChar,50)

                 };

            parameters[0].Value = catalog;

            List<Accounts> actls = new List<Accounts>();

           conn = ConnectionPool.getPool().getConnection();
            SqlCommand cmd = new SqlCommand(strSql.ToString(),conn);
            cmd.Parameters.Add(parameters[0]);
         
           // cmd.ExecuteNonQuery();
          
            SqlDataReader reader = cmd.ExecuteReader();
           
            while (reader.Read())
            {
                Accounts acct = new Accounts();
                acct.Acctcode = reader[0].ToString();
                acct.Acctname = reader[1].ToString();
                acct.Parentid = reader[2].ToString();
                acct.Acctbalancetype = reader[3].ToString();
                acct.Accttype = reader[4].ToString();
                actls.Add(acct);
            }
            ConnectionPool.getPool().closeConnection(conn);
            reader.Close();
            return actls;
      
        }

        public int getChildCntByAcct(string acctcode) {
            SqlDataReader reader = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT ");
                strSql.Append(" [ACCT_CODE] ,[ACCT_NAME], ");
                strSql.Append(" [PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE] FROM [ACCOUNTS]");
                strSql.Append("  where [PARENTID] = @acctcode ");

                SqlParameter[] parameters = { 
                new SqlParameter("@acctcode", SqlDbType.VarChar,50)

                 };

                parameters[0].Value = acctcode;

                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
                cmd.Parameters.Add(parameters[0]);


                reader = cmd.ExecuteReader();
                int count = 0;

                while (reader.Read())
                {
                    count++;
                }

                return count;
            }catch(Exception e){
                throw e;
                return 0;
            }
            finally
            {
                if(reader!=null)
                    reader.Close();
                ConnectionPool.getPool().closeConnection(conn);
            }
        }


        public string getAcctnameByCode(string acctcode)
        {
            SqlDataReader reader = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT ");
                strSql.Append(" [ACCT_CODE] ,[ACCT_NAME], ");
                strSql.Append(" [PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE] FROM [ACCOUNTS]");
                strSql.Append("  where [ACCT_CODE] = @acctcode ");

                SqlParameter[] parameters = { 
                new SqlParameter("@acctcode", SqlDbType.VarChar,50)

                 };

                parameters[0].Value = acctcode;

                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
                cmd.Parameters.Add(parameters[0]);


                reader = cmd.ExecuteReader();
                string acctname = "";
                
                if (reader.Read())
                {
                    acctname = reader[1].ToString();
                }

                return acctname;
            }
            catch (Exception e)
            {
                throw e;
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                ConnectionPool.getPool().closeConnection(conn);
            }
        }

        public string getAcctidByCode(string acctcode)
        {
            SqlDataReader reader = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT ");
                strSql.Append(" [ID] ");
                strSql.Append(" FROM [ACCOUNTS]");
                strSql.Append("  where [ACCT_CODE] = @acctcode ");

                SqlParameter[] parameters = { 
                new SqlParameter("@acctcode", SqlDbType.VarChar,50)

                 };

                parameters[0].Value = acctcode;

                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
                cmd.Parameters.Add(parameters[0]);


                reader = cmd.ExecuteReader();
                string id = "";

                if (reader.Read())
                {
                    id = reader[0].ToString();
                }

                return id;
            }
            catch (Exception e)
            {
                throw e;
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                ConnectionPool.getPool().closeConnection(conn);
            }
        }

        public Accounts getAccountByCode(string acctcode)
        {
            SqlDataReader reader = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" SELECT [ID],[ABID],");
                strSql.Append(" [ACCT_CODE] ,[ACCT_NAME], ");
                strSql.Append(" [PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE],[ACCT_CATALOG] FROM [ACCOUNTS]");
                strSql.Append("  where [ACCT_CODE] = @acctcode ");

                SqlParameter[] parameters = { 
                new SqlParameter("@acctcode", SqlDbType.VarChar,50)

                 };

                parameters[0].Value = acctcode;

                conn = ConnectionPool.getPool().getConnection();
                SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
                cmd.Parameters.Add(parameters[0]);


                reader = cmd.ExecuteReader();
                Accounts acct = new Accounts();

                if (reader.Read())
                {
                    acct.Id = Convert.ToInt32(reader[0].ToString());
                    acct.Acctcode = reader[2].ToString();
                    acct.Abid = Convert.ToInt32(reader[1].ToString());
                    acct.Acctname = reader[3].ToString();
                    acct.Parentid = reader[4].ToString();
                    acct.Acctbalancetype = reader[5].ToString();
                    acct.Accttype = reader[6].ToString();
                    acct.Acctcatalog = reader[7].ToString();
                }

                return acct;
            }
            catch (Exception e)
            {
                throw e;
                return null;
            }
            finally
            {
                if(reader!=null)
                    reader.Close();
                ConnectionPool.getPool().closeConnection(conn);
            }
        }

        public int del(string code)
        {
            String sql = "delete from [ACCOUNTS] where [ACCT_CODE]=@acctCode";
            SqlParameter[] parameters = { 
                new SqlParameter("@acctCode",SqlDbType.VarChar,50)
            };
            parameters[0].Value = code;

            conn = ConnectionPool.getPool().getConnection();
            SqlCommand cmd = new SqlCommand(sql, ConnectionPool.getPool().getConnection());
            cmd.Parameters.Add(parameters[0]);

            int rows = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            ConnectionPool.getPool().closeConnection(conn);
            return rows;  
        }

        public List<Accounts>  getAcctAll(){

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            strSql.Append(" [ACCT_CODE] ,[ACCT_NAME], ");
            strSql.Append(" [PARENTID] , [ACCT_BALANCE_TYPE], [ACCT_TYPE] FROM [ACCOUNTS]");

            List<Accounts> actls = new List<Accounts>();

            conn = ConnectionPool.getPool().getConnection();
            SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Accounts acct = new Accounts();
                acct.Acctcode = reader[0].ToString();
                acct.Acctname = reader[1].ToString();
                acct.Parentid = reader[2].ToString();
                acct.Acctbalancetype = reader[3].ToString();
                acct.Accttype = reader[4].ToString();
                actls.Add(acct);
            }
            reader.Close();
            ConnectionPool.getPool().closeConnection(conn);
            return actls;
        }
    }
}
